////////////////////////////////////////////////////////////////////////////////
// Copyright 2005 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   TASK
// AUTHOR           :   Ric Yik
// DATE CREATE      :   2005/12/29
// DESCRIPTION      :   a DAO file for the function, TASK
//                  :
//                  :
//                  :
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2005/12/29   Ric Yik         1.0                 source is generated.
//
//
//
////////////////////////////////////////////////////////////////////////////////
package com.erp.app.setup.taskMaster.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.erp.app.common.ProjectConstants;
import com.erp.app.common.util.ProjectCommonUtil;
import com.erp.app.setup.taskMaster.pk.TaskDtlPk;
import com.erp.app.setup.taskMaster.pk.TaskHdrPk;
import com.erp.app.setup.taskMaster.vo.TaskDtlVo;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.dao.SimpleSearchMaintStyleDao;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBigInteger;
import com.erp.framework.types.CustomSqlDate;
import com.erp.framework.util.logger.Logger;

/**
 * <p>
 * A <strong>DAO</strong>(Data Access Object) abstracts and encapsulates all access to the persistent store. The DAO manages the
 * connection with the data source to obtain and store data. Also, the DAO implements the access mechanism required to work with
 * the data source, and always provides a uniform API to its clients.
 *
 * @author Ric Yik
 * @version 1.0 2005/12/29
 * @since FrameWork 1.0
 *
 */
public class TaskDtlDao extends SimpleSearchMaintStyleDao {
    /**
     * Returns an array list of child VOs by the parent
     *
     * @param parent
     *            the incoming parent VO
     * @param conn
     *            the database connection
     * @return an array list of child VOs
     *
     * @author Ric Yik
     * @since FrameWork 1.0
     */
    public ChildList selectByParent(SimpleBaseVo parent, Connection conn) throws FindRecordException {
        return super.selectByParent(parent, TaskDtlVo.class, conn);
    }

    /**
     * Constructs a string of SQL insert statement
     *
     * @return a string of SQL insert statement
     *
     * @author Ric Yik
     * @since FrameWork 1.0
     */
    protected String sqlInsert() {
        String sql = "INSERT INTO T_TASK_DTL (" + "SEQ,COMPANY_CODE, DOC_TYPE, TASK_CODE, TASK_DESC, USER_GROUP, "
                + "USER_CODE, DATE_TYPE, NO_DATE_FROM, DURATION, STATUS, "
                + "IS_DEFAULT, DC_IND, DC_DATE, CREATE_USER, CREATE_DATE, " + "UPDATE_USER, UPDATE_DATE) " + "VALUES ("
                + "?,?, ?, ?, ?, ?, " + "?, ?, ?, ?, ?, " + "?, 'NO', ?, ?, SYSDATE, " + "?, ?)";
        //super.logSqlQuery(sql);
        return sql;
    }

    /**
     * Sets the values for a SQL insert or update statement.
     *
     * @param pStmt
     *            the incoming prepared statement
     * @param obj
     *            the incoming VO
     *
     * @author Ric Yik
     * @since FrameWork 1.0
     */
    protected void setValuesForInsertAndModify(PreparedStatement pStmt, Object obj, String opType) throws SQLException {
        TaskDtlVo vo = (TaskDtlVo) obj;
        int i = 1;
        pStmt.setString(i++, vo.getDtlSeq());
        pStmt.setString(i++, vo.getCompanyCode());
        pStmt.setString(i++, vo.getDocType());
        pStmt.setString(i++, vo.getTaskCode());
        setNullableString(pStmt, i++, vo.getDtlTaskDesc());
        setNullableString(pStmt, i++, vo.getDtlUserGroup());
        setNullableString(pStmt, i++, vo.getDtlUserCode());
        setNullableString(pStmt, i++, vo.getDtlDateType());
        setNullableBigInteger(pStmt, i++, vo.getDtlNoDateFrom());
        setNullableBigInteger(pStmt, i++, vo.getDtlDuration());
        setNullableString(pStmt, i++, vo.getDtlStatus());
        setNullableString(pStmt, i++, vo.getDtlIsDefault());
        if (ProjectConstants.DB_OP_UPDATE.equals(opType))
            setNullableString(pStmt, i++, vo.getDtlDcInd());
        setNullableDate(pStmt, i++, vo.getDtlDcDate());
        setNullableString(pStmt, i++, vo.getCreateUser());
        if (ProjectConstants.DB_OP_UPDATE.equals(opType))
            setNullableTimestamp(pStmt, i++, vo.getCreateDate());
        setNullableString(pStmt, i++, vo.getUpdateUser());
        if (ProjectConstants.DB_OP_INSERT.equals(opType))
            setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
        if (ProjectConstants.DB_OP_UPDATE.equals(opType)) {
            pStmt.setString(i++, vo.getCompanyCode());
            pStmt.setString(i++, vo.getDocType());
            pStmt.setString(i++, vo.getTaskCode());
            setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
        }
    }

    /**
     * Sets the values for a SQL insert statement.
     *
     * @param pStmt
     *            the incoming prepared statement
     * @param obj
     *            the incoming VO
     *
     * @author Ric Yik
     * @since FrameWork 1.0
     */
    protected void setValuesForInsert(PreparedStatement pStmt, Object obj) throws SQLException {
        setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_INSERT);
    }

    /**
     * Constructs a string of SQL update statement
     *
     * @return a string of SQL update statement
     *
     * @author Ric Yik
     * @since FrameWork 1.0
     */
    protected String sqlUpdate() {
        String sql = "UPDATE T_TASK_DTL SET SEQ=?,COMPANY_CODE=?, DOC_TYPE=?, TASK_CODE=?, TASK_DESC=?, USER_GROUP=?, "
                + "USER_CODE=?, DATE_TYPE=?, NO_DATE_FROM=?, DURATION=?, STATUS=?, "
                + "IS_DEFAULT=?, DC_IND=?, DC_DATE=?, CREATE_USER=?, CREATE_DATE=?, " + "UPDATE_USER=?, UPDATE_DATE=SYSDATE "
                + "WHERE COMPANY_CODE=? AND DOC_TYPE=? AND TASK_CODE=? " + "AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";
        //super.logSqlQuery(sql);
        return sql;
    }

    /**
     * Sets the values for a SQL update statement.
     *
     * @param pStmt
     *            the incoming prepared statement
     * @param obj
     *            the incoming VO
     *
     * @author Ric Yik
     * @since FrameWork 1.0
     */
    protected void setValuesForModify(PreparedStatement pStmt, Object obj) throws SQLException {
        setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_UPDATE);
    }

    /**
     * Constructs a string of SQL delete statement
     *
     * @return a string of SQL update statement
     *
     * @author Ric Yik
     * @since FrameWork 1.0
     */
    protected String sqlDelete() {
        String sql = "DELETE FROM T_TASK_DTL WHERE COMPANY_CODE=? AND DOC_TYPE=? AND TASK_CODE=? ";
        //super.logSqlQuery(sql);
        return sql;
    }

    /**
     * Sets the values for a SQL delete statement.
     *
     * @param pStmt
     *            the incoming prepared statement
     * @param obj
     *            the incoming VO
     *
     * @author Ric Yik
     * @since FrameWork 1.0
     */
    protected void setValuesForDelete(PreparedStatement pStmt, Object obj) throws SQLException {
        TaskDtlVo vo = (TaskDtlVo) obj;
        int i = 1;
        pStmt.setString(i++, vo.getCompanyCode());
        pStmt.setString(i++, vo.getDocType());
        pStmt.setString(i++, vo.getTaskCode());
    }

    /**
     * Constructs a string of SQL select statement
     *
     * @return a string of SQL select statement
     *
     * @author Ric Yik
     * @since FrameWork 1.0
     */
    protected String sqlFind() {
        String sql = "SELECT COMPANY_CODE, DOC_TYPE, TASK_CODE, TASK_DESC, USER_GROUP, "
                + "USER_CODE, DATE_TYPE, NO_DATE_FROM, DURATION, STATUS,SEQ, "
                + "IS_DEFAULT, DC_IND, DC_DATE, CREATE_USER, CREATE_DATE, " + "UPDATE_USER, UPDATE_DATE " + "FROM T_TASK_DTL "
                + "WHERE COMPANY_CODE=? AND DOC_TYPE=? AND TASK_CODE=? " + "ORDER BY COMPANY_CODE, DOC_TYPE, TASK_CODE";
        //super.logSqlQuery(sql);
        return sql;
    }

    /**
     * Sets the values for a SQL select statement for finding by primary key.
     *
     * @param pStmt
     *            the incoming prepared statement
     * @param obj
     *            the incoming PK
     *
     * @author Ric Yik
     * @since FrameWork 1.0
     */
    protected void setValuesForFind(PreparedStatement pStmt, Object obj) throws SQLException {
        TaskDtlPk pk = (TaskDtlPk) obj;
        int i = 1;
        pStmt.setString(i++, pk.getCompanyCode());
        pStmt.setString(i++, pk.getDocType());
        pStmt.setString(i++, pk.getTaskCode());
    }

    /**
     * Constructs a string of SQL select statement
     *
     * @return a string of SQL select statement
     *
     * @author Ric Yik
     * @since FrameWork 1.0
     */
    protected String sqlList() {
        return this.sqlList(false, false);
    }

    protected String sqlListWithActiveOnly(boolean getDefault) {
        return this.sqlList(getDefault, true);
    }

    /**
     * Constructs a string of SQL select statement
     *
     * @return a string of SQL select statement
     *
     * @author Ric Yik
     * @since FrameWork 1.0
     */
    protected String sqlList(boolean getDefault, boolean getActiveOnly) {
        String sql = "SELECT COMPANY_CODE, DOC_TYPE, TASK_CODE, TASK_DESC, USER_GROUP, "
                + "USER_CODE, DATE_TYPE, NO_DATE_FROM, DURATION, STATUS,SEQ, "
                + "IS_DEFAULT, DC_IND, DC_DATE, CREATE_USER, CREATE_DATE, " + "UPDATE_USER, UPDATE_DATE " + "FROM T_TASK_DTL "
                + "WHERE COMPANY_CODE=? AND DOC_TYPE=? "
                + (getDefault ? "AND IS_DEFAULT = '" + ProjectConstants.GENERIC_YES + "' " : " ")
                + (getActiveOnly ? "AND STATUS = '" + ProjectConstants.GENERIC_ACTIVE + "' " : " ")
                + " ORDER BY COMPANY_CODE, DOC_TYPE, TASK_CODE";
        //super.logSqlQuery(sql);
        return sql;
    }

    /**
     * Sets the values for a SQL select statement for finding child tables.
     *
     * @param pStmt
     *            the incoming prepared statement
     * @param obj
     *            the incoming parent PK
     *
     * @author Ric Yik
     * @since FrameWork 1.0
     */
    protected void setValuesForList(PreparedStatement pStmt, Object obj) throws SQLException {
        TaskHdrPk pk = (TaskHdrPk) obj;
        int i = 1;
        pStmt.setString(i++, pk.getCompanyCode());
        pStmt.setString(i++, pk.getDocType());
        if (pk.isGetDefault()) {
            pStmt.setString(i++, ProjectCommonUtil.toGenericYesNo(pk.isGetDefault()));
        }
    }

    /**
     * Sets the values of a result set to a vo
     *
     * @param rs
     *            the incoming result set
     * @return a vo containing the result set values
     *
     * @author Ric Yik
     * @since FrameWork 1.0
     */
    protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {
        TaskDtlVo vo = new TaskDtlVo();
        vo.setCompanyCode(rs.getString("COMPANY_CODE"));
        vo.setDocType(rs.getString("DOC_TYPE"));
        vo.setTaskCode(rs.getString("TASK_CODE"));
        vo.setDtlTaskDesc(rs.getString("TASK_DESC"));
        vo.setDtlUserGroup(rs.getString("USER_GROUP"));
        vo.setDtlUserCode(rs.getString("USER_CODE"));
        vo.setDtlDateType(rs.getString("DATE_TYPE"));
        vo.setDtlNoDateFrom(CustomBigInteger.customize(rs.getBigDecimal("NO_DATE_FROM")));
        vo.setDtlDuration(CustomBigInteger.customize(rs.getBigDecimal("DURATION")));
        vo.setDtlStatus(rs.getString("STATUS"));
        vo.setDtlIsDefault(rs.getString("IS_DEFAULT"));
        vo.setDtlDcInd(rs.getString("DC_IND"));
        vo.setDtlDcDate(CustomSqlDate.customize(rs.getDate("DC_DATE")));
        vo.setCreateUser(rs.getString("CREATE_USER"));
        vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
        vo.setUpdateUser(rs.getString("UPDATE_USER"));
        vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
        vo.setDtlSeq(rs.getString("SEQ"));
        return vo;
    }

    public ArrayList getTaskDetails(TaskHdrPk parentKey, Connection conn) throws Exception {
        ArrayList rslt = null;
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {
            String query = this.sqlListWithActiveOnly(parentKey.isGetDefault());
            Logger.sql("getTaskDetails(), query=" + query);

            pStmt = conn.prepareStatement(query);
            pStmt.setString(1, parentKey.getCompanyCode());
            pStmt.setString(2, parentKey.getDocType());

            rs = pStmt.executeQuery();

            if (rs != null) {
                rslt = new ArrayList();
                while (rs.next()) {
                    rslt.add(this.getResultSetValues(rs));
                }
            }
        } catch (Exception e) {
            Logger.logExceptions(this, e);
            throw new FindRecordException(e.getMessage());
        } finally {
            try {
                if (pStmt != null)
                    pStmt.close();
                if (rs != null)
                    rs.close();
            } catch (Exception e) {
            }
        }
        return rslt;
    }
}
